from main import db
from sqlalchemy.exc import InvalidRequestError
from main.utils import JSONHelper
from datetime import datetime, date
from sqlalchemy import or_, and_, func, extract
from main.models import Lab, LabSubscribe


def query_lab_list():
    qs = Lab.query.filter(Lab.status == True)
    return qs.order_by(Lab.create_time.desc()).all()


def count_lab_usedNum(id):
    return LabSubscribe.query.filter(LabSubscribe.lab_id == id).count()


def query_lab_by_id(id):
    return Lab.query.filter(Lab.id == id).first()


def query_labSubscribe_list(condition):
    qs = LabSubscribe.query.filter()
    if 'subscribe_id' in condition:
        qs = qs.filter(LabSubscribe.subscribe_id == condition['subscribe_id'])
    if 'examine_status' in condition:
        qs = qs.filter(LabSubscribe.examine_status == condition['examine_status'])
    if 'date' in condition:
        date = datetime.strptime(condition['date'], '%Y-%m-%d')
        qs = qs.filter(db.cast(LabSubscribe.create_time, db.DATE) == date)
    return qs.order_by(LabSubscribe.create_time.desc()).all()


def query_labSubscribe_by_labId(labId):
    return LabSubscribe.query.filter(LabSubscribe.lab_id == labId).first()


def query_labSubscribe_by_id(id):
    return LabSubscribe.query.filter(LabSubscribe.id == id).first()


def query_labSubscribe_by_userId_labId(userId, labId):
    return LabSubscribe.query.filter(and_(LabSubscribe.lab_id == labId, LabSubscribe.subscribe_id == userId)).first()


def query_labSubscribe_by_week(date):
    date = datetime.strptime(date, '%Y-%m-%d')
    return LabSubscribe.query.filter(extract('week', LabSubscribe.create_time) == date.strftime('%W'),
                                     extract('year', LabSubscribe.create_time) == date.year).count()


def query_labSubscribe_by_month(date):
    date = datetime.strptime(date, '%Y-%m-%d')
    return LabSubscribe.query.filter(extract('month', LabSubscribe.create_time) == date.month,
                                     extract('year', LabSubscribe.create_time) == date.year).count()


if __name__ == '__main__':
    date = '2020-03-10'
    # date = datetime.strptime(date, '%Y-%m-%d')
    print(query_labSubscribe_by_month(date))
